﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using UCNKompetenceBors.Model;

namespace UCNKompetenceBors.Database
{
    public class dbInformation
    {
        /// <summary>
        /// Gets the information, based on the pageId parameter.
        /// </summary>
        /// <param name="pageId">Specifies the ID of the page to be used.</param>
        /// <returns>Returns a Dictionary<string, string> containing key-valuepairs of control IDs and texts.</returns>
        public static Dictionary<string, string> GetInformationByPageId(string pageId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    Dictionary<string, string> informationMap = new Dictionary<string, string>();

                    string query = "select informationText, informationControlID from Information where informationPageID = '" + pageId + "'";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        informationMap.Add(dr["informationControlID"].ToString(), dr["informationText"].ToString());
                    }

                    con.Close();
                    con.Dispose();

                    return informationMap;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets the localized information, based on the pageId and culture parameters.
        /// </summary>
        /// <param name="pageId">Specifies the ID of the page to be used.</param>
        /// <param name="culture">Specifies the culture to be used.</param>
        /// <returns>Returns a Dictionary<string, string> containing key-valuepairs of control IDs and texts.</returns>
        public static Dictionary<string, string> GetInformationByPageId(string pageId, string culture)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    Dictionary<string, string> informationMap = new Dictionary<string, string>();

                    string query = "select loc.informationLocalizedText, inf.informationControlID " +
                                   "from Information inf, InformationLocalized loc " +
                                   "where inf.informationPageID = '" + pageId + "'" +
                                   "and loc.informationLocale = '" + culture + "'" +
                                   "and loc.informationID = inf.informationID";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        informationMap.Add(dr["informationControlID"].ToString(), dr["informationLocalizedText"].ToString());
                    }

                    con.Close();
                    con.Dispose();

                    return informationMap;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a text, based on the pageId and controlId parameters.
        /// </summary>
        /// <param name="pageId">Specifies the ID of the page to be used.</param>
        /// <param name="controlId">Specifies the ID of the control to be used.</param>
        /// <returns>Returns a string containing the resulting text.</returns>
        public static string GetInformationByPageIdAndControlId(string pageId, string controlId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "select informationText, informationControlID " +
                                   "from Information " +
                                   "where informationPageID = '" + pageId + "' " +
                                   "and informationControlID = '" + controlId + "'";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    string information = "";

                    while (dr.Read())
                    {
                        information = dr["informationText"].ToString();
                    }

                    con.Close();
                    con.Dispose();

                    return information;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a text, based on the pageId, controlId and culture parameters.
        /// </summary>
        /// <param name="pageId">Specifies the ID of the page to be used.</param>
        /// <param name="controlId">Specifies the ID of the control to be used.</param>
        /// <param name="culture">Specifies the culture to be used.</param>
        /// <returns>Returns a string containing the resulting text.</returns>
        public static string GetInformationByPageIdAndControlId(string pageId, string controlId, string culture)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "select loc.informationLocalizedText, inf.informationControlID " +
                                   "from Information inf, InformationLocalized loc " +
                                   "where inf.informationPageID = '" + pageId + "' " +
                                   "and loc.informationLocale = '" + culture + "' " +
                                   "and inf.informationControlID = '" + controlId + "' " +
                                   "and loc.informationID = inf.informationID";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    string information = "";

                    while (dr.Read())
                    {
                        information = dr["informationLocalizedText"].ToString();
                    }

                    con.Close();
                    con.Dispose();

                    return information;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a distinct list of page IDs.
        /// </summary>
        /// <returns>Returns a list of strings containing page IDs.</returns>
        public static List<string> GetPageIdsDistinct()
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<string> pageIds = new List<string>();

                    string query = "select distinct informationPageID from Information";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        string pageId = dr["informationPageID"].ToString();

                        pageIds.Add(pageId);
                    }

                    con.Close();
                    con.Dispose();

                    return pageIds;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a list of control IDs, based on the pageId parameter.
        /// </summary>
        /// <param name="pageId">Specifies the ID of the page to be used.</param>
        /// <returns>Returns a list of strings containing control IDs.</returns>
        public static List<Information> GetControlsByPageId(string pageId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<Information> controls = new List<Information>();

                    string query = "select inf.informationDescription, inf.informationControlID, loc.informationLocalizedDescription from Information inf, InformationLocalized loc where inf.informationPageID = @pageID and loc.informationID = inf.informationID";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@pageID", pageId);

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        Information inf = new Information();

                        inf.InformationDescription = dr["informationDescription"].ToString();
                        inf.InformationControlID = dr["informationControlID"].ToString();
                        inf.InformationLocalizedDescription = dr["informationLocalizedDescription"].ToString();

                        controls.Add(inf);
                    }

                    con.Close();
                    con.Dispose();

                    return controls;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Updates the information, based on the input parameters.
        /// </summary>
        /// <param name="informationText">Specifies the text to be used.</param>
        /// <param name="controlId">Specifies the ID of the control to be used.</param>
        /// <param name="pageId">Specifies the ID of the page to be used.</param>
        public static void UpdateInformation(string informationText, string controlId, string pageId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "update Information set informationText = @informationText where informationControlID = @controlID AND informationPageID = @pageID";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    informationText = informationText.Replace(Environment.NewLine, "<br />");

                    cmd.Parameters.AddWithValue("@informationText", informationText);
                    cmd.Parameters.AddWithValue("@controlID", controlId);
                    cmd.Parameters.AddWithValue("@pageID", pageId);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }
    }
}